
global statereg C:\Users\chris\Dropbox\1Publication and Research\2022 - Regulation and employment\
set scheme s1color



/**************

benchmarking

descriptive results
- fact 1: increase in restrictions
- fact 2: concentration of the increase (occupations)
- fact 3: concentration of the increase (states)

baseline CPS regressions
baseline OEWS regressions

cleaning:
- clean ipums cps monthly
- clean state reg
- clean OEWS
- clean qcew
- clean ipums census
- clean social explorer

************/

*----------------------------------- benchmarking

* compare with Trudeau and Timmons report
import excel using "$statereg\data\licensing data\MasterData_SOLI2023.xlsx",clear firstrow sheet("Results")
ren State state
run "$statereg\analysis\state_cwalk.do"
keep state OverallRank- RegionalRank st
save "$statereg\data\licensing data\MasterData_SOLI2023.dta",replace

use "$statereg\data\soc_state_data_clean.dta",clear
keep if year==2022 | year==2021
collapse (sum) reg*,by(st)
merge 1:1 st using "$statereg\data\licensing data\MasterData_SOLI2023.dta"
keep if _merge==3
drop _merge
merge m:1 st using "$statereg\data\state2019.dta"
keep if _merge==3
drop _merge
gen lreg_restrict=log(reg_restrict)

corr reg_restrict BarrierScore LicensesScore
corr lreg_restrict BarrierScore LicensesScore

corr lreg_restrict BarrierScore
local temp: di %3.2f r(rho)
twoway (lfitci lreg_restrict BarrierScore) (scatter lreg_restrict BarrierScore,msymbol(Oh)), ytitle("log(Regulatory Restrictions)") xtitle("Trudeau and Timmons (2023)") legend(off) note(Correlation = `temp') subtitle("Panel A: Barrier Score")
graph export "$statereg\drafts\figs\scatter_lregul_barrier.pdf", as(pdf) replace

corr lreg_restrict LicensesScore
local temp: di %3.2f r(rho)
twoway (lfitci lreg_restrict LicensesScore) (scatter lreg_restrict LicensesScore,msymbol(Oh)), ytitle("log(Regulatory Restrictions)") xtitle("Trudeau and Timmons (2023)") legend(off) note(Correlation = `temp') subtitle("Panel B: Licenses Score")
graph export "$statereg\drafts\figs\scatter_lregul_license.pdf", as(pdf) replace

*look at states that had universal licensing reforms

montana, arizona, pennsylvania in 2019
utah, iowa, idaho,  in 2020
south dakota, colorado, wyoming, missisippi, kansas, oklahoma in 2021

use "$statereg\data\soc_state_data_clean.dta",clear
egen uid=group(st soc2)
tsset uid year
gen dlreg_restrict=(reg_restrict-L.reg_restrict)/L.reg_restrict
winsor2 dl*,trim cuts(1 99) replace
gen lreg_restrict=log(reg_restrict)
gen year2017=cond(year==2017,1,0)
gen year2018=cond(year==2018,1,0)
gen year2019=cond(year==2019,1,0)
gen year2020=cond(year==2020,1,0)
gen year2021=cond(year==2021,1,0)
gen year2022=cond(year==2022,1,0)

*AZ missing 2019, 2018?
reg lreg_restrict year2020 year2021 year2022 if state=="AZ",r

reg lreg_restrict year2021 year2022 if state=="IA",r

reg lreg_restrict year2019 year2021 year2022 if state=="ID",r

reg lreg_restrict year2019 year2020 year2022 if state=="KS",r


*----------------------------------- descriptive results

* fact 1: increase in restrictions
use "$statereg\data\soc_state_data_clean.dta",clear
collapse (sum) reg*,by(year)
replace reg_restrict=reg_restrict/1000000

graph bar reg_restrict, over(year) ytitle("Regulatory Restrictions (in millions)") subtitle("Panel A: Unbalanced Panel")
graph export "$statereg\drafts\figs\bar_restrictions_year.pdf", as(pdf) replace

//robustness using a consistent panel of 13 states
use "$statereg\data\soc_state_data_clean.dta",clear
collapse (sum) reg*,by(st year)
keep if year>=2019 & year<=2022
bysort st: gen nobs=_N
keep if nobs>=3
collapse (sum) reg*,by(year)
replace reg_restrict=reg_restrict/1000000

graph bar reg_restrict, over(year) ytitle("Regulatory Restrictions (in millions)") subtitle("Panel B: Balanced Panel")
graph export "$statereg\drafts\figs\bar_restrictions_year_balanced.pdf", as(pdf) replace

*heat maps
use "$statereg\data\soc_state_data_clean.dta",clear
keep if year>=2020 & year<=2022
collapse (sum) reg*,by(st)
run "$statereg\analysis\state_maptile.do"

maptile reg_restrict,geo(state) twopt(title("Panel A: Average Regulatory Restrictions"))
graph export "$statereg\drafts\figs\spatial_reg_restrict.pdf", as(pdf) replace

use "$statereg\data\soc_state_data_clean.dta",clear
keep if year>=2020 & year<=2022
collapse (sum) reg*,by(st year)
egen tid=group(year)
tsset st tid
local togrow "reg_restrict"
quietly foreach var in `togrow'{
	gen dl`var'=(`var'-L.`var')/L.`var'
}
collapse dlreg_restrict reg_restrict,by(st)
run "$statereg\analysis\state_maptile.do"
corr dlreg_restrict reg_restrict

maptile dlreg_restrict,geo(state) twopt(title("Panel B: Average Regulatory Growth"))
graph export "$statereg\drafts\figs\spatial_dlreg_restrict.pdf", as(pdf) replace


* fact 2: concentration of the increase (occupations)
use "$statereg/data/oews/state_all_dl.dta",clear
merge m:1 st soc2 year using "$statereg\data\soc_state_data_clean.dta"
keep if _merge==3
drop _merge
keep if year>=2020 & year<=2022
collapse (sum) reg_restrict TOT_EMP (mean) H_* [aw=TOT_EMP],by(soc2 year)
keep if year==2020 | year==2022
egen tid=group(year)
tsset soc2 tid
local togrow "reg_restrict H_MEDIAN"
quietly foreach var in `togrow'{
	gen dl`var'=(`var'-L.`var')/L.`var'
}
gen lTOT_EMP=log(TOT_EMP)
gen lH_MEDIAN=log(H_MEDIAN)
gen lH_PCT75=log(H_PCT75)
gen lH_PCT25=log(H_PCT25)
gen lH_PCT75_net25=lH_PCT75-lH_PCT25
gen lreg_restrict=log(reg_restrict)
winsor2 dl*,replace trim cuts(1 99)


corr dlreg_restrict lH_PCT75_net25 [aw=TOT_EMP]


corr dlreg_restrict lH_PCT75_net25 [aw=TOT_EMP]
local temp: di %3.2f r(rho)
twoway (lfitci dlreg_restrict lH_PCT75_net25 [aw=TOT_EMP]) (scatter dlreg_restrict lH_PCT75_net25 [aw=TOT_EMP],msymbol(Oh)), ytitle("Regulatory Growth (2020-2022)") xtitle("log 75th to 25th Wage Gap") legend(off) note(Correlation = `temp') subtitle("Panel A: Regulation and 75-25 Wage Gap")
graph export "$statereg\drafts\figs\scatter_dlreg_lh7525.pdf", as(pdf) replace


corr dlreg_restrict dlH_MEDIAN [aw=TOT_EMP]
local temp: di %3.2f r(rho)
twoway (lfitci dlreg_restrict dlH_MEDIAN [aw=TOT_EMP]) (scatter dlreg_restrict dlH_MEDIAN [aw=TOT_EMP],msymbol(Oh)), ytitle("Regulatory Growth (2020-2022)") xtitle("Median Wages Growth (2020-2022)") legend(off) note(Correlation = `temp') subtitle("Panel A: Regulation and Wage Growth")
graph export "$statereg\drafts\figs\scatter_dlreg_dlhmedian.pdf", as(pdf) replace

* fact 3: concentration of the increase (states)
use "$statereg\data\soc_state_data_clean.dta",clear
keep if year>=2020 & year<=2022
collapse (sum) reg*,by(st year)
egen tid=group(year)
tsset st tid
local togrow "reg_restrict"
quietly foreach var in `togrow'{
	gen dl`var'=(`var'-L.`var')/L.`var'
}
collapse dlreg_restrict reg_restrict,by(st)
merge m:1 st using "$statereg\data\state_2016_election.dta"
keep if _merge==3
drop _merge
merge m:1 st using "$statereg\data\state2019.dta"
keep if _merge==3
drop _merge
gen lreg_restrict=log(reg_restrict)

corr lreg_restrict trump_percent [aw=totpop]
local temp: di %3.2f r(rho)
twoway (lfitci lreg_restrict trump_percent [aw=totpop]) (scatter lreg_restrict trump_percent [aw=totpop],msymbol(Oh)), ytitle("log(Regulation) (2020-2022)") xtitle("GOP Vote Share") legend(off) note(Correlation = `temp') subtitle("Panel A: Regulation and Politics")
graph export "$statereg\drafts\figs\scatter_lreg_gopvote.pdf", as(pdf) replace

corr dlreg_restrict trump_percent [aw=totpop]
local temp: di %3.2f r(rho)
twoway (lfitci dlreg_restrict trump_percent [aw=totpop]) (scatter dlreg_restrict trump_percent [aw=totpop],msymbol(Oh)), ytitle("Regulatory Growth (2020-2022)") xtitle("GOP Vote Share") legend(off) note(Correlation = `temp') subtitle("Panel B: Regulation Growth and Politics")
graph export "$statereg\drafts\figs\scatter_dlreg_gopvote.pdf", as(pdf) replace


*----------------------------------- baseline OEWS regressions

use "$statereg/data/oews/state_all_dl.dta",clear
merge m:1 st soc2 year using "$statereg\data\soc_state_data_clean.dta"
keep if _merge==3
drop _merge
merge m:1 st using "$statereg\data\mclaughlin_wong_data.dta"
keep if _merge==3
drop _merge
merge m:1 st soc2 using "$statereg\data\ipums_census_clean.dta"
keep if _merge==3
drop _merge
gen soc2stshare_age=soc2stshare*stateage
gen lincwage=log(incwage)
gen ltotsoc2st=log(totsoc2st)
gen lTOT_EMP=log(TOT_EMP)
gen lH_MEDIAN=log(H_MEDIAN)
gen lH_PCT75=log(H_PCT75)
gen lH_PCT25=log(H_PCT25)
gen lH_PCT75_net25=lH_PCT75-lH_PCT25

reg lTOT_EMP lreg_restrict,cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasstyr "No",replace
est sto temp1
reghdfe lTOT_EMP lreg_restrict,a(st year) vce(cluster st)
estadd local hasX "No",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasstyr "No",replace
est sto temp2
reghdfe lTOT_EMP lreg_restrict,a(st#year) vce(cluster st)
estadd local hasX "No",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasstyr "Yes",replace
est sto temp3
reg lH_MEDIAN lreg_restrict,cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasstyr "No",replace
est sto temp4
reghdfe lH_MEDIAN lreg_restrict,a(st year) vce(cluster st)
estadd local hasX "No",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasstyr "No",replace
est sto temp5
reghdfe lH_MEDIAN lreg_restrict,a(st#year) vce(cluster st)
estadd local hasX "No",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasstyr "Yes",replace
est sto temp6
reg lH_PCT75 lreg_restrict,cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasstyr "No",replace
est sto temp7
reghdfe lH_PCT75 lreg_restrict,a(st year) vce(cluster st)
estadd local hasX "No",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasstyr "No",replace
est sto temp8
reghdfe lH_PCT75 lreg_restrict,a(st#year) vce(cluster st)
estadd local hasX "No",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasstyr "Yes",replace
est sto temp9
reg lH_PCT25 lreg_restrict,cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasstyr "No",replace
est sto temp10
reghdfe lH_PCT25 lreg_restrict,a(st year) vce(cluster st)
estadd local hasX "No",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasstyr "No",replace
est sto temp11
reghdfe lH_PCT25 lreg_restrict,a(st#year) vce(cluster st)
estadd local hasX "No",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasstyr "Yes",replace
est sto temp12

la var lreg_restrict "log(Regulation)"
local toorder "lreg_restrict"
cd "$statereg\drafts\tabs"
esttab temp1 temp2 temp3 temp4 temp5 temp6 temp7 temp8 temp9 temp10 temp11 temp12 using baseline_regul.tex, b(3) replace star(x 0.20 * 0.10 ** 0.05 *** 0.01) mtitles("(1)" "(2)" "(3)" "(4)" "(5)" "(6)" "(7)" "(8)" "(9)" "(10)" "(11)" "(12)") nonum brackets se mgroups("log(Employment)" "log(Median Hourly)" "log(75th Hourly)" "log(25th Hourly)", pattern(1 0 0 1 0 0 1 0 0 1 0 0) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) label keep(`toorder') order(`toorder') stats (r2 N hasyr hasst hasstyr, label("R-squared" "Sample Size" "Year FE" "State FE" "State x Year") fmt(2 0)) parentheses nolz fragment nogaps nolines


*IV results: Lewbel approach

global demog1980 famsize nchild age male married race_white race_black //educ_lesshs educ_hs educ_somecoll educ_collpl //lgdp_chained linc_capita lpop euclid

capture drop lreg_restrict_resid
reg lreg_restrict $demog1980 i.st i.year,r 
predict lreg_restrict_resid,resid

*interact with these exogenous terms
gen lreg_restrict_resid_famsize=lreg_restrict_resid*famsize
gen lreg_restrict_resid_nchild=lreg_restrict_resid*nchild
gen lreg_restrict_resid_age=lreg_restrict_resid*age
gen lreg_restrict_resid_male=lreg_restrict_resid*male
gen lreg_restrict_resid_married=lreg_restrict_resid*married
gen lreg_restrict_resid_race_white=lreg_restrict_resid*race_white
gen lreg_restrict_resid_race_black=lreg_restrict_resid*race_black
gen lreg_restrict_resid_lesshs=lreg_restrict_resid*educ_lesshs
gen lreg_restrict_resid_hs=lreg_restrict_resid*educ_hs
gen lreg_restrict_resid_somecoll=lreg_restrict_resid*educ_somecoll
gen lreg_restrict_resid_collpl=lreg_restrict_resid*educ_collpl

global IVs lreg_restrict_resid lreg_restrict_resid_famsize lreg_restrict_resid_nchild lreg_restrict_resid_age lreg_restrict_resid_male lreg_restrict_resid_married lreg_restrict_resid_race_white lreg_restrict_resid_race_black //lreg_restrict_resid_lesshs lreg_restrict_resid_hs lreg_restrict_resid_somecoll lreg_restrict_resid_collpl

reghdfe lTOT_EMP lreg_restrict,a(st year) vce(robust)
estadd local hasX "No",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasIV "No",replace
est sto temp1
reghdfe lTOT_EMP lreg_restrict $demog1980,a(st year) vce(robust)
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasIV "No",replace
est sto temp2
ivregress gmm lTOT_EMP $demog1980  i.st i.year (lreg_restrict=$IVs),r
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasIV "Yes",replace
est sto temp3
reghdfe lH_MEDIAN lreg_restrict,a(st year) vce(robust)
estadd local hasX "No",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasIV "No",replace
est sto temp4
reghdfe lH_MEDIAN lreg_restrict $demog1980,a(st year) vce(robust)
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasIV "No",replace
est sto temp5
ivregress gmm lTOT_EMP $demog1980  i.st i.year (lreg_restrict=$IVs),r
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasIV "Yes",replace
est sto temp6

la var lreg_restrict "log(Regulation)"
local toorder "lreg_restrict"
cd "$statereg\drafts\tabs"
esttab temp1 temp2 temp3 temp4 temp5 temp6 using robust_iv.tex, b(3) replace star(x 0.20 * 0.10 ** 0.05 *** 0.01) mtitles("(1)" "(2)" "(3)" "(4)" "(5)" "(6)" "(7)" "(8)" "(9)" "(10)" "(11)" "(12)") nonum brackets se mgroups("log(Employment)" "log(Median Hourly)", pattern(1 0 0 1 0 0 1 0 0 1 0 0) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) label keep(`toorder') order(`toorder') stats (r2 N hasX hasst hasyr hasIV, label("R-squared" "Sample Size" "Controls" "State FE" "Year FE" "Instrument?") fmt(2 0)) parentheses nolz fragment nogaps nolines



*----------------------------------- baseline CPS regressions

use "$statereg\data\ipums_monthly_clean.dta",clear
gen soc2=substr(soc2010,1,2)
destring soc2,replace
drop if soc2==.
merge m:1 st soc2 year using "$statereg\data\soc_state_data_clean.dta"
keep if _merge==3
drop _merge
merge m:1 st year using "$statereg\data\state_data_clean.dta"
keep if _merge==3
drop _merge
gen learnweek=log(earnweek)
gen lregst_restrict=log(regst_restrict)
gen lreg_restrict_procert=lreg_restrict*has_procert_license
gen lregst_restrict_procert=lregst_restrict*has_procert_license
egen indid=group(ind1990)
quietly sum reg_restrict,d 
gen highreg=cond(`r(p50)'>reg_restrict,1,0)

*summary statistics

global sumstats male race_white_nonhispan race_white_hispan race_black married yrschool famsize age has_procert_license employed earnweek reg_restrict 

quietly estpost sum $sumstats [aw=wtfinl],d
est sto temp1
quietly estpost sum $sumstats if highreg==1 [aw=wtfinl],d
est sto temp2
quietly estpost sum $sumstats if highreg==0 [aw=wtfinl],d
est sto temp3


la var male "Male"
la var race_white_nonhispan "White"
la var race_white_hispan "Hispanic"
la var race_black "Black"
la var married "Married"
la var yrschool "Years of Education"
la var famsize "Family Size"
la var age "Age"
la var has_procert_license "Is Licensed"
la var employed "Is Employed"
la var earnweek "Weekly Earnings"
la var reg_restrict "State $\times$ Occ. Reg"
local toorder "male race_white_nonhispan race_white_hispan race_black married yrschool famsize age has_procert_license employed earnweek reg_restrict"
local dig2 "2 2 2 2 2 1 1 1 2 2 0 0"
cd "$statereg\drafts\tabs"
esttab temp1 temp2 temp3 using sumstats.tex, cells("mean(fmt(`dig2'))  sd(fmt(`dig2' ))")  label collabels("Mean" "SD") mgroups("All" "High Regulation" "Low Regulation", pattern(1 1 1) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) keep(`toorder') order(`toorder') nonum replace fragment


global demogX male race_white_nonhispan race_white_hispan race_black married yrschool famsize age

*main results
reg employed lregst_restrict has_procert_license lregst_restrict_procert [aw=wtfinl],cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp1
reg employed lregst_restrict has_procert_license lregst_restrict_procert $demogX [aw=wtfinl],cluster(st)
estadd local hasX "Yes",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp2
reghdfe employed lregst_restrict has_procert_license lregst_restrict_procert $demogX [aw=wtfinl],a(st indid month year) vce(cluster st)
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasind "Yes",replace
estadd local hasstyr "No",replace
est sto temp3
reg learnweek lreg_restrict has_procert_license lreg_restrict_procert if employed==1 [aw=earnwt],cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp4
reg learnweek lreg_restrict has_procert_license lreg_restrict_procert $demogX if employed==1 [aw=earnwt],cluster(st)
estadd local hasX "Yes",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp5
reghdfe learnweek lreg_restrict has_procert_license lreg_restrict_procert $demogX if employed==1 [aw=earnwt],a(st indid month year) vce(cluster st)
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasind "Yes",replace
estadd local hasstyr "No",replace
est sto temp6

la var lreg_restrict "log(State x Occupation Restrictions)"
la var lregst_restrict "log(State Restrictions)"
la var has_procert_license "Has Professional License"
la var lregst_restrict_procert "\quad $\times$ Has Professional License"
la var lreg_restrict_procert "\quad $\times$ Has Professional License"
local toorder "has_procert_license lregst_restrict lregst_restrict_procert lreg_restrict lreg_restrict_procert"
cd "$statereg\drafts\tabs"
esttab temp1 temp2 temp3 temp4 temp5 temp6 using cps_regul.tex, b(3) replace star(* 0.10 ** 0.05 *** 0.01) mtitles("(1)" "(2)" "(3)" "(4)" "(5)" "(6)" "(7)" "(8)" "(9)" "(10)" "(11)" "(12)") nonum brackets se mgroups("Is Employed" "log(Weekly Earnings)", pattern(1 0 0 1 0 0) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) label keep(`toorder') order(`toorder') stats (r2 N hasX hasyr hasst hasind, label("R-squared" "Sample Size" "Demographic Controls" "Year FE" "State FE" "Industry FE") fmt(2 0)) parentheses nolz fragment nogaps nolines


*robustness with tenure results
reghdfe employed lregst_restrict has_procert_license lregst_restrict_procert jtyears $demogX [aw=wtfinl],a(st indid month year) vce(cluster st)
reghdfe employed lregst_restrict has_procert_license lregst_restrict_procert $demogX if jtyears!=. [aw=wtfinl],a(st indid month year) vce(cluster st)

*robustness: using logs on RHS variable
gen lyrschool=log(yrschool+1)
gen lfamsize=log(famsize+1)
gen lage=log(age)

global logdemogX male race_white_nonhispan race_white_hispan race_black married lyrschool lfamsize lage

*main results
reg employed lregst_restrict has_procert_license lregst_restrict_procert [aw=wtfinl],cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp1
reg employed lregst_restrict has_procert_license lregst_restrict_procert $logdemogX [aw=wtfinl],cluster(st)
estadd local hasX "Yes",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp2
reghdfe employed lregst_restrict has_procert_license lregst_restrict_procert $logdemogX [aw=wtfinl],a(st indid month year) vce(cluster st)
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasind "Yes",replace
estadd local hasstyr "No",replace
est sto temp3
reg learnweek lreg_restrict has_procert_license lreg_restrict_procert if employed==1 [aw=earnwt],cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp4
reg learnweek lreg_restrict has_procert_license lreg_restrict_procert $logdemogX if employed==1 [aw=earnwt],cluster(st)
estadd local hasX "Yes",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp5
reghdfe learnweek lreg_restrict has_procert_license lreg_restrict_procert $logdemogX if employed==1 [aw=earnwt],a(st indid month year) vce(cluster st)
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasind "Yes",replace
estadd local hasstyr "No",replace
est sto temp6


la var lreg_restrict "log(State x Occupation Restrictions)"
la var lregst_restrict "log(State Restrictions)"
la var has_procert_license "Has Professional License"
la var lregst_restrict_procert "\quad $\times$ Has Professional License"
la var lreg_restrict_procert "\quad $\times$ Has Professional License"
la var male "Male"
la var race_white_nonhispan "White"
la var race_white_hispan "Hispanic"
la var race_black "Black"
la var married "Married"
la var lyrschool "log(Years of Schooling)"
la var lfamsize "log(Family Size)"
la var lage "log(Age)"
local toorder "has_procert_license lregst_restrict lregst_restrict_procert lreg_restrict lreg_restrict_procert male race_white_nonhispan race_white_hispan race_black married lyrschool lfamsize lage"
cd "$statereg\drafts\tabs"
esttab temp1 temp2 temp3 temp4 temp5 temp6 using cps_regul_logs.tex, b(3) replace star(* 0.10 ** 0.05 *** 0.01) mtitles("(1)" "(2)" "(3)" "(4)" "(5)" "(6)" "(7)" "(8)" "(9)" "(10)" "(11)" "(12)") nonum brackets se mgroups("Is Employed" "log(Weekly Earnings)", pattern(1 0 0 1 0 0) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) label keep(`toorder') order(`toorder') stats (r2 N hasX hasyr hasst hasind, label("R-squared" "Sample Size" "Demographic Controls" "Year FE" "State FE" "Industry FE") fmt(2 0)) parentheses nolz fragment nogaps nolines

*robustness: control for tenure
global demogXten jtyears male race_white_nonhispan race_white_hispan race_black married yrschool famsize age

reg employed lregst_restrict has_procert_license lregst_restrict_procert if jtyears!=. [aw=wtfinl],cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp1
reg employed lregst_restrict has_procert_license lregst_restrict_procert $demogXten [aw=wtfinl],cluster(st)
estadd local hasX "Yes",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp2
reghdfe employed lregst_restrict has_procert_license lregst_restrict_procert $demogXten [aw=wtfinl],a(st indid month year) vce(cluster st)
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasind "Yes",replace
estadd local hasstyr "No",replace
est sto temp3
reg learnweek lreg_restrict has_procert_license lreg_restrict_procert if employed==1 & jtyears!=. [aw=earnwt],cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp4
reg learnweek lreg_restrict has_procert_license lreg_restrict_procert $demogXten if employed==1 [aw=earnwt],cluster(st)
estadd local hasX "Yes",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
estadd local hasstyr "No",replace
est sto temp5
reghdfe learnweek lreg_restrict has_procert_license lreg_restrict_procert $demogXten if employed==1 [aw=earnwt],a(st indid month year) vce(cluster st)
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasind "Yes",replace
estadd local hasstyr "No",replace
est sto temp6

la var lreg_restrict "log(State x Occupation Restrictions)"
la var lregst_restrict "log(State Restrictions)"
la var has_procert_license "Has Professional License"
la var lregst_restrict_procert "\quad $\times$ Has Professional License"
la var lreg_restrict_procert "\quad $\times$ Has Professional License"
la var jtyears "Years of Tenure"
local toorder "has_procert_license lregst_restrict lregst_restrict_procert lreg_restrict lreg_restrict_procert jtyears"
cd "$statereg\drafts\tabs"
esttab temp1 temp2 temp3 temp4 temp5 temp6 using cps_regul_tenure.tex, b(3) replace star(* 0.10 ** 0.05 *** 0.01) mtitles("(1)" "(2)" "(3)" "(4)" "(5)" "(6)" "(7)" "(8)" "(9)" "(10)" "(11)" "(12)") nonum brackets se mgroups("Is Employed" "log(Weekly Earnings)", pattern(1 0 0 1 0 0) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) label keep(`toorder') order(`toorder') stats (r2 N hasX hasyr hasst hasind, label("R-squared" "Sample Size" "Demographic Controls" "Year FE" "State FE" "Industry FE") fmt(2 0)) parentheses nolz fragment nogaps nolines

*robustness: other interactions
gen lregst_restrict_male=lregst_restrict*male
gen lregst_restrict_race_white=lregst_restrict*race_white_nonhispan
gen lregst_restrict_hispanic=lregst_restrict*race_white_hispan
gen lregst_restrict_black=lregst_restrict*race_black
gen lregst_restrict_married=lregst_restrict*married
gen lregst_restrict_yrschool=lregst_restrict*yrschool
gen lregst_restrict_famsize=lregst_restrict*famsize
gen lregst_restrict_age=lregst_restrict*age
gen lreg_restrict_male=lreg_restrict*male
gen lreg_restrict_race_white=lreg_restrict*race_white_nonhispan
gen lreg_restrict_hispanic=lreg_restrict*race_white_hispan
gen lreg_restrict_black=lreg_restrict*race_black
gen lreg_restrict_married=lreg_restrict*married
gen lreg_restrict_yrschool=lreg_restrict*yrschool
gen lreg_restrict_famsize=lreg_restrict*famsize
gen lreg_restrict_age=lreg_restrict*age
egen tid=group(year month)


global demogX male race_white_nonhispan race_white_hispan race_black married yrschool famsize age
global interact1 lregst_restrict_male lregst_restrict_race_white lregst_restrict_hispanic lregst_restrict_black lregst_restrict_married lregst_restrict_yrschool lregst_restrict_famsize lregst_restrict_age
global interact2 lreg_restrict_male lreg_restrict_race_white lreg_restrict_hispanic lreg_restrict_black lreg_restrict_married lreg_restrict_yrschool lreg_restrict_famsize lreg_restrict_age

reg employed lregst_restrict has_procert_license lregst_restrict_procert [aw=wtfinl],cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
est sto temp1
reg employed lregst_restrict has_procert_license lregst_restrict_procert $demogX i.tid [aw=wtfinl],cluster(st)
estadd local hasX "Yes",replace
estadd local hasst "No",replace
estadd local hasyr "Yes",replace
estadd local hasind "No",replace
est sto temp2
reg employed lregst_restrict has_procert_license lregst_restrict_procert $interact1 $demogX i.tid [aw=wtfinl],cluster(st)
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasind "Yes",replace
est sto temp3
reg learnweek lreg_restrict has_procert_license lreg_restrict_procert if employed==1 [aw=earnwt],cluster(st)
estadd local hasX "No",replace
estadd local hasst "No",replace
estadd local hasyr "No",replace
estadd local hasind "No",replace
est sto temp4
reg learnweek lreg_restrict has_procert_license lreg_restrict_procert $demogX i.tid if employed==1 [aw=earnwt],cluster(st)
estadd local hasX "Yes",replace
estadd local hasst "No",replace
estadd local hasyr "Yes",replace
estadd local hasind "No",replace
est sto temp5
reg learnweek lreg_restrict has_procert_license lreg_restrict_procert $interact2 $demogX i.tid if employed==1 [aw=earnwt],cluster(st)
estadd local hasX "Yes",replace
estadd local hasst "Yes",replace
estadd local hasyr "Yes",replace
estadd local hasind "Yes",replace
est sto temp6

la var lregst_restrict "log(State Restrictions)"
la var has_procert_license "Has Professional License"
la var lregst_restrict_procert "\quad Has Professional License"
la var lregst_restrict_male "\quad $\times$ Male"
la var lregst_restrict_race_white "\quad $\times$ White"
la var lregst_restrict_hispanic "\quad $\times$ Hispanic"
la var lregst_restrict_black "\quad $\times$ Black"
la var lregst_restrict_married "\quad $\times$ Married"
la var lregst_restrict_yrschool "\quad $\times$ Schooling"
la var lregst_restrict_famsize "\quad $\times$ Family Size"
la var lregst_restrict_age "\quad $\times$ Age"
local toorder "has_procert_license lregst_restrict lregst_restrict_procert lregst_restrict_male lregst_restrict_race_white lregst_restrict_hispanic lregst_restrict_black lregst_restrict_married lregst_restrict_yrschool lregst_restrict_famsize lregst_restrict_age"
cd "$statereg\drafts\tabs"
esttab temp1 temp2 temp3 using cps_regul_int1.tex, b(3) replace star(* 0.10 ** 0.05 *** 0.01) mtitles("(1)" "(2)" "(3)" "(4)" "(5)" "(6)" "(7)" "(8)" "(9)" "(10)" "(11)" "(12)") nonum brackets se mgroups("Is Employed" "log(Weekly Earnings)", pattern(1 0 0 1 0 0) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) label keep(`toorder') order(`toorder') stats (r2 N hasX hasyr, label("R-squared" "Sample Size" "Demographic Controls" "Year-Month FE") fmt(2 0)) parentheses nolz fragment nogaps nolines

la var lreg_restrict "log(State x Occupation Restrictions)"
la var has_procert_license "Has Professional License"
la var lreg_restrict_procert "\quad Has Professional License"
la var lreg_restrict_male "\quad $\times$ Male"
la var lreg_restrict_race_white "\quad $\times$ White"
la var lreg_restrict_hispanic "\quad $\times$ Hispanic"
la var lreg_restrict_black "\quad $\times$ Black"
la var lreg_restrict_married "\quad $\times$ Married"
la var lreg_restrict_yrschool "\quad $\times$ Schooling"
la var lreg_restrict_famsize "\quad $\times$ Family Size"
la var lreg_restrict_age "\quad $\times$ Age"
local toorder "has_procert_license lreg_restrict lreg_restrict_procert lreg_restrict_male lreg_restrict_race_white lreg_restrict_hispanic lreg_restrict_black lreg_restrict_married lreg_restrict_yrschool lreg_restrict_famsize lreg_restrict_age"
cd "$statereg\drafts\tabs"
esttab temp4 temp5 temp6 using cps_regul_int2.tex, b(3) replace star(* 0.10 ** 0.05 *** 0.01) mtitles("(1)" "(2)" "(3)" "(4)" "(5)" "(6)" "(7)" "(8)" "(9)" "(10)" "(11)" "(12)") nonum brackets se mgroups("log(Weekly Earnings)", pattern(1 0 0 1 0 0) prefix(\multicolumn{@span}{c}{) suffix(}) span erepeat(\cmidrule(lr){@span})) label keep(`toorder') order(`toorder') stats (r2 N hasX hasyr, label("R-squared" "Sample Size" "Demographic Controls" "Year-Month FE") fmt(2 0)) parentheses nolz fragment nogaps nolines


*-----------------------------------------  clean ipums cps monthly

*occupational licensing, 2018-2024 monthly

use "$statereg\data\ipums_monthly.dta",clear
ren statefip st
gen employed=cond(empstat==10,1,0)
drop if famsize==0 // missing
gen male=cond(sex==1,1,0)
drop if sex==9
keep if age>=25 & age<=65
*construct three non overlapping groups for race
gen race_white_nonhispan=cond(race==100 & hispan==0,1,0)
gen race_white_hispan=cond(race==100 & hispan>=100 & hispan<=500,1,0)
gen race_black=cond(race==200,1,0)
gen married=cond(marst==1 | marst==2,1,0)
drop sex race marst empstat hispan
replace uhrswork1=ahrsworkt if uhrswork1==997
drop if uhrswork1==999 & employed==1
drop if uhrswork1==0 & employed==1
drop ahrsworkt
gen has_procert_license=.
replace has_procert_license=0 if profcert==1
replace has_procert_license=1 if profcert==2
gen has_govcert_license=.
replace has_govcert_license=0 if statecert==1
replace has_govcert_license=1 if statecert==2
gen has_jobcert=.
replace has_jobcert=0 if jobcert==1
replace has_jobcert=1 if jobcert==2
gen unioncovered=1 if union==3 | union==2
replace unioncovered=0 if union==1
gen selfemployed=cond(classwkr==10 | classwkr==13 | classwkr==14,1,0)
replace selfemployed=. if classwkr==99
gen yrschool=.
replace yrschool=0 if educ==2
replace yrschool=1 if educ==11
replace yrschool=2 if educ==12 | educ==10
replace yrschool=3 if educ==13
replace yrschool=4 if educ==14
replace yrschool=5 if educ==21 | educ==20
replace yrschool=6 if educ==22
replace yrschool=7 if educ==31 | educ==30
replace yrschool=8 if educ==32
replace yrschool=9 if educ==40
replace yrschool=10 if educ==50
replace yrschool=11 if educ==60
replace yrschool=12 if educ==70 | educ==71 | educ==72 | educ==73 // high school - unclear, diploma, or equivalent
replace yrschool=13 if educ==80 | educ==81 // some college, no degree
replace yrschool=14 if educ==91 | educ==92 // associates degree
replace yrschool=15 if educ==100
replace yrschool=16 if educ==110 | educ==111 | educ==121 // 4 years, bachelors degree, 
replace yrschool=18 if educ==124 | educ==123 // masters/professional
replace yrschool=20 if educ==125 // phd
gen educ_postgrad=0
replace educ_postgrad=1 if educ==125 | educ==124 | educ==123
gen educ_college=0
replace educ_college=1 if educ==122 | educ==121 | educ==111 | educ==120 | educ==110
gen educ_assoc=0
replace educ_assoc=1 if educ==92 | educ==91
gen educ_somecoll=0
replace educ_somecoll=1 if educ==100 | educ==90 | educ==81 | educ==80
gen educ_hs=0
replace educ_hs=1 if educ==73
gen educ_nohs=0
replace educ_nohs=1 if educ>=10 & educ<=72
gen sameemployer=1 if empsame==2
replace sameemployer=0 if empsame==1
gen cbsa_5mpl=cond(cbsasz==6,1,0)
gen cbsa_25_49=cond(cbsasz==5,1,0)
gen cbsa_1_24=cond(cbsasz==4,1,0)
gen cbsa_500_999=cond(cbsasz==3,1,0)
gen cbsa_249_499=cond(cbsasz==2,1,0)
gen cbsa_100_249=cond(cbsasz==1,1,0)
la var cbsa_5mpl "5 million or more"
la var cbsa_25_49 "2.5-4.99 million"
la var cbsa_1_24 "1-2.49 million"
la var cbsa_500_999 "500-999k"
la var cbsa_249_499 "250-499k"
la var cbsa_100_249 "100-249k"
replace earnweek=. if earnweek>=9999
drop if earnweek==. & employed==1

*job tenure variables
replace jtyears=. if jtyears>99

merge m:1 year month using "$statereg\data\stlouis_pcemonthly.dta"
keep if _merge==3
drop _merge
replace earnweek=earnweek*100/pcedefl
drop if earnweek==. & employed==1
gen wage=earnweek/uhrswork1
drop if uhrswork1==. | wage<2

*crosswalk OCC2010 to SOC2010 and bring in IT data
merge m:1 occ2010 using "$statereg\data\occ2010_to_soc2010.dta"
keep if _merge==3 | _merge==1
drop _merge
replace soc2010=substr(soc2010,1,6)
replace soc2010="11-201" if occ2010==30 // advertising managers
replace soc2010="11-312" if occ2010==130 // human resource managers
replace soc2010="11-906" if occ2010==320 // funeral directors
replace soc2010="13-104" if occ2010==560 // compliance officers, except agriculture
replace soc2010="13-107" if occ2010==620 // human resources, training
replace soc2010="13-112" if occ2010==720 // meeting and convention planners
replace soc2010="13-119" if occ2010==730 // other business ops managers
replace soc2010="15-112" if occ2010==1000 // computer scientists and system analysts
replace soc2010="15-114" if occ2010==1100 // network and computer system admins
replace soc2010="15-203" if occ2010==1230 // statisticians (coding as operations research bc statistics not in onet)
replace soc2010="19-305" if occ2010==1830 // urban and regional planners
replace soc2010="19-409" if occ2010==1960 // life physical and social science tech
replace soc2010="21-109" if occ2010==2020 // community and social service specialists
replace soc2010="23-201" if occ2010==2140 // paralegals and legal assistants
replace soc2010="23-209" if occ2010==2150 // legal support workers nec
replace soc2010="29-114" if occ2010==3130 // registered nurses
replace soc2010="31-901" if occ2010==3240 // therapists nec
replace soc2010="29-101" if occ2010==3410 // health diagnosing and treating practitioners
replace soc2010="29-209" if occ2010==3530 // health technologists nec
replace soc2010="31-909" if occ2010==3650 // medical assistants and other healthcare
replace soc2010="33-301" if occ2010==3950 // law enforcement workers nec
replace soc2010="47-509" if occ2010==6940 // extraction workers nec
replace soc2010="49-209" if occ2010==7125 // electronic workers nec
replace soc2010="51-511" if occ2010==8230 // bookbinders, printing machine operators
replace soc2010="53-301" if occ2010==9100 // bus and ambulance drivers
drop if soc2010=="" & employed==1
replace soc2010="19-104" if soc2010=="19-10X"
replace soc2010="21-202" if soc2010=="21-209"
replace soc2010="25-101" if soc2010=="25-100"
replace soc2010="25-301" if soc2010=="25-300"
drop profcert statecert jobcert educ pcedefl union cbsasz empsame 
save "$statereg\data\ipums_monthly_clean.dta",replace

*-----------------------------------------  clean state reg

insheet using "$statereg\data\licensing data\2023_regulation_restrictions.csv",clear
gen reg_restrict=probability*restrictions
gen reg_words=probability*words
ren jurisdiction state
gen year=substr(date_collected,1,4)
gen month=substr(date_collected,6,2)
destring year,replace
ren soc_code soc2
collapse (sum) reg_*,by(state soc2 year)


*dont combine statutes with others; also its only available in 2022
insheet using "$statereg\data\soc_state_data.csv",clear
*dont do this: replace jurisdiction = subinstr(jurisdiction, "_statutes", "",.) // include statutes
gen reg_restrict=probability*restrictions
gen reg_words=probability*words
ren jurisdiction state
gen year=substr(date_collected,1,4)
gen month=substr(date_collected,6,2)
destring year,replace
ren soc_code soc2
collapse (sum) reg_*,by(state soc2 year)
replace state=proper(state)
egen uid=group(state soc2)
gen lreg_restrict=log(reg_restrict)
gen lreg_words=log(reg_words)
tsset uid year
gen lag1_lreg_restrict=L1.lreg_restrict
gen lag1_lreg_words=L1.lreg_words
run "$statereg\analysis\state_cwalk.do"
save "$statereg\data\soc_state_data_clean.dta",replace
renvars reg_restrict reg_words \ regst_restrict regst_words
collapse (sum) regst*,by(st year)
save "$statereg\data\state_data_clean.dta",replace

insheet using "$statereg\data\mclaughlin_wong_data.csv",clear
run "$statereg\analysis\state_cwalk.do"
collapse (mean) age euclid gdp_chained inc_capita pop,by(st)
gen lgdp_chained=log(gdp_chained)
gen linc_capita=log(inc_capita)
gen lpop=log(pop)
ren age stateage
save "$statereg\data\mclaughlin_wong_data.dta",replace


*--------------------------------------------- clean OEWS

cd "$statereg/data/oews/" // now 2022 has a different censor amt
forval t=2022/2022{
	import excel using state_M`t'_dl.xlsx, clear ///
					firstrow sheet("data")
	ren AREA_TITLE state
	ren OCC_CODE soc2010 
	keep if O_GROUP=="detailed"
	keep state soc2010 TOT_EMP H_PCT10- H_PCT90 A_PCT10- A_PCT90
	replace TOT_EMP="" if TOT_EMP=="**"
	
	local torep "H_PCT10 H_PCT25 H_MEDIAN H_PCT75 H_PCT90"
	quietly foreach var in `torep'{
		replace `var'="115" if `var'=="#"
		replace `var'="" if `var'=="*" | `var'=="**"
	}

	local torep "A_PCT10 A_PCT25 A_MEDIAN A_PCT75 A_PCT90"
		quietly foreach var in `torep'{
		replace `var'="239200" if `var'=="#"
		replace `var'="" if `var'=="*" | `var'=="**"
		}
	quietly destring,replace
	gen year=`t'
	save state_M`t'_dl.dta,replace
	}

cd "$statereg/data/oews/" // now 2022 has a different censor amt
forval t=2020/2021{
	import excel using state_M`t'_dl.xlsx, clear ///
					firstrow sheet("data")
	ren AREA_TITLE state
	ren OCC_CODE soc2010 
	keep if O_GROUP=="detailed"
	keep state soc2010 TOT_EMP H_PCT10- H_PCT90 A_PCT10- A_PCT90
	replace TOT_EMP="" if TOT_EMP=="**"
	
	local torep "H_PCT10 H_PCT25 H_MEDIAN H_PCT75 H_PCT90"
	quietly foreach var in `torep'{
		replace `var'="100" if `var'=="#"
		replace `var'="" if `var'=="*" | `var'=="**"
	}

	local torep "A_PCT10 A_PCT25 A_MEDIAN A_PCT75 A_PCT90"
		quietly foreach var in `torep'{
		replace `var'="208000" if `var'=="#"
		replace `var'="" if `var'=="*" | `var'=="**"
		}
	quietly destring,replace
	gen year=`t'
	save state_M`t'_dl.dta,replace
	}
	
cd "$statereg/data/oews/" // slightly different typo format w/ capitalization
forval t=2019/2019{
	import excel using state_M`t'_dl.xlsx, clear ///
					firstrow sheet("data")
	ren area_title state
	ren occ_code soc2010 
	keep if o_group=="detailed"
	keep state soc2010 tot_emp h_pct10- h_pct90 a_pct10- a_pct90
	ren tot_emp TOT_EMP
	renvars h_pct10 h_pct25 h_median h_pct75 h_pct90 \ H_PCT10 H_PCT25 H_MEDIAN H_PCT75 H_PCT90
	renvars a_pct10 a_pct25 a_median a_pct75 a_pct90 \ A_PCT10 A_PCT25 A_MEDIAN A_PCT75 A_PCT90
	replace TOT_EMP="" if TOT_EMP=="**"
	
	local torep "H_PCT10 H_PCT25 H_MEDIAN H_PCT75 H_PCT90"
	quietly foreach var in `torep'{
		replace `var'="100" if `var'=="#"
		replace `var'="" if `var'=="*" | `var'=="**"
	}

	local torep "A_PCT10 A_PCT25 A_MEDIAN A_PCT75 A_PCT90"
		quietly foreach var in `torep'{
		replace `var'="208000" if `var'=="#"
		replace `var'="" if `var'=="*" | `var'=="**"
		}
	quietly destring,replace
	gen year=`t'
	save state_M`t'_dl.dta,replace
	}


cd "$statereg/data/oews/" 
forval t=2017/2018{
	import excel using state_M`t'_dl.xlsx, clear ///
					firstrow sheet("data")
	ren STATE state
	ren OCC_CODE soc2010 
	keep if OCC_GROUP=="detailed"
	keep state soc2010 TOT_EMP H_PCT10- H_PCT90 A_PCT10- A_PCT90
	replace TOT_EMP="" if TOT_EMP=="**"
	
	local torep "H_PCT10 H_PCT25 H_MEDIAN H_PCT75 H_PCT90"
	quietly foreach var in `torep'{
		replace `var'="100" if `var'=="#"
		replace `var'="" if `var'=="*" | `var'=="**"
	}

	local torep "A_PCT10 A_PCT25 A_MEDIAN A_PCT75 A_PCT90"
		quietly foreach var in `torep'{
		replace `var'="208000" if `var'=="#"
		replace `var'="" if `var'=="*" | `var'=="**"
		}
	quietly destring,replace
	gen year=`t'
	save state_M`t'_dl.dta,replace
	}
	
	
use "$statereg/data/oews/state_M2017_dl.dta",clear
append using "$statereg/data/oews/state_M2018_dl.dta"
append using "$statereg/data/oews/state_M2019_dl.dta"
append using "$statereg/data/oews/state_M2020_dl.dta"
append using "$statereg/data/oews/state_M2021_dl.dta"
append using "$statereg/data/oews/state_M2022_dl.dta"
run "$statereg\analysis\state_cwalk.do"
merge m:1 year using "$statereg\data\stlouis_pcedeflannual.dta"
keep if _merge==3
drop _merge
local todefl "H_PCT10 H_PCT25 H_MEDIAN H_PCT75 H_PCT90 A_PCT10 A_PCT25 A_MEDIAN A_PCT75 A_PCT90"
quietly foreach var in `todefl'{
	replace `var'=`var'*100/pcedefl
}
gen soc2=substr(soc2010,1,2)
collapse (sum) TOT_EMP (mean) H_PCT10- A_PCT90 [aw=TOT_EMP],by(soc2 st year)
destring soc2,replace
egen uid=group(soc2 st)
tsset year uid
gen dlTOT_EMP=(TOT_EMP-L.TOT_EMP)/L.TOT_EMP
gen dlH_MEDIAN=(H_MEDIAN-L.H_MEDIAN)/L.H_MEDIAN
winsor2 dlTOT_EMP dlH_MEDIAN, trim cuts(1 99) replace
save "$statereg/data/oews/state_all_dl.dta",replace


*--------------------------------------------- clean qcew

*june 7 full qcew comes out

*keep state * 3 digit industry
cd "$statereg\data\"
local years "2019 2020 2021"
quietly foreach var in `years' {
insheet using `var'.q1-q4.singlefile.csv,clear
// keep state total and state*industry
keep if agglvl_code==51 | agglvl_code==55
drop lq_disclosure_code-lq_avg_wkly_wage
// keep the cells that dont have a disclosure problem
keep if disclosure_code==""
renvars month1_emplvl month2_emplvl month3_emplvl oty_month1_emplvl_pct_chg oty_month2_emplvl_pct_chg oty_month3_emplvl_pct_chg qtrly_estabs avg_wkly_wage  oty_avg_wkly_wage_pct_chg \ emp_m1 emp_m2 emp_m3 dlemp_m1 dlemp_m2 dlemp_m3 estabs weekly_wage dlweekly_wage
save qcew_county_`var'.dta,replace
}


*----------------------------------------- clean ipums census

*2010 5yr state, regulation and licensing
use "$statereg\data\ipums_census.dta",clear
keep if empstat==1
keep if age>=25 & age<=65
gen male=cond(sex==1,1,0)
gen married=cond(marst==1,1,0)
gen race_white=cond(race==1,1,0)
gen race_black=cond(race==2,1,0)
gen educ_lesshs=cond(educd>=0 & educd<=61,1,0)
gen educ_hs=cond(educd==62 | educd==63 | educd==64,1,0)
gen educ_somecoll=cond(educd>=65 & educd<=83,1,0)
gen educ_collpl=cond(educd>=101 & educd<=116,1,0)
keep if incwage>=15000
gen soc2=substr(occsoc,1,2)
destring soc2,replace
bysort st: gen totst=sum(perwt)
bysort st soc2: gen totsoc2st=sum(perwt)
collapse (mean) famsize nchild age male married race_* educ_* incwage totst totsoc2st [aw=perwt],by(soc2 st)
sum totsoc2st,d
gen soc2stshare=totsoc2st/totst
ren statefip st
save "$statereg\data\ipums_census_clean.dta",replace

*1980 state, regulation and licensing





*----------------------------------------- clean social explorer

*2019: https://www.socialexplorer.com/tables/ACS2019/R12868934
quietly infile using "$statereg\data\state2019dic.dct", using("$statereg\data\state2019data.txt") clear
ren STATE st
destring st,replace
ren A00001_001 totpop
gen popdens=totpop/A00003_001
gen male=A02001_002/A02001_001
gen age_under18=(A01001_002+A01001_003+A01001_004+A01001_005)/A01001_001
gen age_18_24=A01001_006/A01001_001
gen age_25_34=A01001_007/A01001_001
gen age_35_44=A01001_008/A01001_001
gen age_45_54=A01001_009/A01001_001
gen age_55_64=A01001_010/A01001_001
gen age_65_74=A01001_011/A01001_001
gen age_75_84=A01001_012/A01001_001
gen age_85pl=A01001_013/A01001_001
gen race_white=A03001_002/A03001_001
gen race_black=A03001_003/A03001_001
gen race_nativeam=A03001_004/A03001_001
gen race_asian=A03001_005/A03001_001
gen married=A11001_003/A11001_001
gen educ_lesshs=A12001_002/A12001_001
gen educ_hs=A12001_003/A12001_001
gen educ_somecoll=A12001_004/A12001_001
gen educ_coll=A12001_005/A12001_001
gen educ_collpl=(A12001_006+A12001_007+A12001_008)/A12001_001
gen ind_agmining=A17004_002/A17004_001
gen ind_const=A17004_003/A17004_001
gen ind_manuf=A17004_004/A17004_001
gen ind_wholesale=A17004_005/A17004_001
gen ind_retail=A17004_006/A17004_001
gen ind_transport=A17004_007/A17004_001
gen ind_info=A17004_008/A17004_001
gen ind_FIRE=A17004_009/A17004_001
gen ind_pro=A17004_010/A17004_001
gen ind_educhealth=A17004_011/A17004_001
gen ind_artentertain=A17004_012/A17004_001
gen ind_otherserve=A17004_013/A17004_001
gen ind_public=A17004_014/A17004_001
gen occ_mgmtbiz=B17008_002/B17008_001
gen occ_pro=B17008_003/B17008_001
gen occ_healthsupp=B17008_004/B17008_001
gen occ_protective=B17008_005/B17008_001
gen occ_food=B17008_006/B17008_001
gen occ_building=B17008_007/B17008_001
gen occ_personalcare=B17008_008/B17008_001
gen occ_sales=B17008_009/B17008_001
gen occ_officeadmin=B17008_010/B17008_001
gen occ_farming=B17008_011/B17008_001
gen occ_constextract=B17008_012/B17008_001
gen occ_production=B17008_013/B17008_001
gen occ_transport=B17008_014/B17008_001
gen income_less10k=A14001_002/A14001_001
gen income_10k15=A14001_003/A14001_001
gen income_15k20=A14001_004/A14001_001
gen income_20k25=A14001_005/A14001_001
gen income_25k30=A14001_006/A14001_001
gen income_30k35=A14001_007/A14001_001
gen income_35k40=A14001_008/A14001_001
gen income_40k45=A14001_009/A14001_001
gen income_45k50=A14001_010/A14001_001
gen income_50k60=A14001_011/A14001_001
gen income_60k75=A14001_012/A14001_001
gen income_75k100=A14001_013/A14001_001
gen income_100k125=A14001_014/A14001_001
gen income_125k150=A14001_015/A14001_001
gen income_150kpl=(A14001_016+A14001_017)/A14001_001
ren A14006_001 medhhincome
keep st age* race* educ* male married popdens totpop medhhincome ind_*
save "$statereg\data\state2019.dta",replace
export delimited using "$statereg\data\state2019.csv", replace

*1900: https://www.socialexplorer.com/tables/Census1900/R13772653
quietly infile using "$statereg\data\socialexplorer\state1900dic.dct", using("$statereg\data\socialexplorer\state1900data.txt") clear
replace QName="Alaska" if QName=="Alaska Territory"
replace QName="Arizona" if QName=="Arizona Territory"
replace QName="Hawaii" if QName=="Hawaii Territory"
replace QName="New Mexico" if QName=="New Mexico Territory"
replace QName="Oklahoma" if QName=="Oklahoma Territory"
ren QName state
run "$statereg\analysis\state_cwalk.do"
gen shfarms1900=T052_002/T052_001
ren A001_001 popdens1900
ren T001_001 pop1900
gen male1900=T005_002/T005_001
gen white1900=T006_001/T005_001
gen illiterate1900=T025_002/T025_001
keep st shfarms1900 popdens1900 pop1900 male1900 white1900 illiterate1900
save "$statereg\data\socialexplorer\state1900.dta",replace

*1980: https://www.socialexplorer.com/tables/C1980/R13772662
quietly infile using "$statereg\data\socialexplorer\state1980dic.dct", using("$statereg\data\socialexplorer\state1980data.txt") clear
ren STATE st
destring st,replace
ren T001_001 totpop
gen popdens=totpop/A00003_001
gen male=T003_002/T003_001
gen age_under18=(T006_002+T006_003+T006_004+T006_005)/T006_001
gen age_18_24=T006_006/T006_001
gen age_25_34=T006_007/T006_001
gen age_35_44=T006_008/T006_001
gen age_45_54=T006_009/T006_001
gen age_55_64=T006_010/T006_001
gen age_65_74=T006_011/T006_001
gen age_75_84=T006_012/T006_001
gen age_85pl=T006_013/T006_001
gen race_white=T012_002/T012_001
gen race_black=T012_003/T012_001
gen race_nativeam=T012_004/T012_001
gen race_asian=T012_005/T012_001
gen married=T026_003/T026_001
gen educ_lesshs=T031_002/T031_001
gen educ_hs=T031_003/T031_001
gen educ_collpl=T031_006/T031_001
gen soc2_11=
keep st age* race* educ* male married popdens totpop medhhincome ind_*
save "$statereg\data\socialexplorer\state1980.dta",replace

